﻿Imports MySql.Data.MySqlClient
Imports System.Text.RegularExpressions
Imports System.Text
Imports System.IO

Public Class citaprevia

    Private Sub citaprevia_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        clinica.Show()
    End Sub

    Dim connectionstring As String
    Dim commandtext As String
    'Private mydataset As DataSet
    Dim adapter As MySqlDataAdapter
    Dim adapterSerch As MySqlDataAdapter
    Dim table As DataTable
    Dim conexion As MySqlConnection

    Dim codCli, nomCli, apeCli, telCli, mailCli, obsCli As String
    Dim codPac, nomPac, tipoPac, raza, obsPac As String

    Private Sub citaprevia_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        connectionstring = "server=localhost;user id=root;password=root;database=zoo;"
        commandtext = "select m.id as idM, m.nombre as mascotas, m.tipo,m.raza, cp.id as idCP,cp.horacita,cp.fechacita,cp.atendido,c.dni,c.nombre,c.apellidos,c.telefono,c.email from cliente c inner join mascota m on c.dni=m.duenio inner join citaprevia cp on cp.mascota=m.id"

        Try
            adapter = New MySqlDataAdapter(commandtext, connectionstring)
            table = New DataTable
            adapter.Fill(table)

            dgv_citPre.DataSource = table
            dgv_citPre.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
            'registro()

            txt_codPac.DataBindings.Add("Text", table, "idM")
            txt_nomPac.DataBindings.Add("Text", table, "mascotas")
            cbox_tipoPac.DataBindings.Add("Text", table, "tipo")
            txt_raza.DataBindings.Add("Text", table, "raza")
            txt_codCli.DataBindings.Add("Text", table, "dni")
            txt_nomCli.DataBindings.Add("Text", table, "nombre")
            txt_apeCli.DataBindings.Add("Text", table, "apellidos")
            txt_telCli.DataBindings.Add("Text", table, "telefono")
            txt_mailCli.DataBindings.Add("Text", table, "email")
            dtp_hora.DataBindings.Add("Text", table, "horacita")
            dtp_fecha.DataBindings.Add("Text", table, "fechacita")
        Catch ex As Exception
            MsgBox("Error en la base de datos")
            Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
            Dim sw As New StreamWriter(fs)
            sw.WriteLine(ex.Message.ToString)
            sw.Close()
            fs.Close()
        End Try

        Dim tipos() As String = {"Perro", "Gato", "Ave", "Reptil", "Roedor", "Otro"}
        For i = 0 To tipos.Length - 1
            cbox_tipoPac.Items.Add(tipos(i))
        Next

    End Sub


    Private Sub txt_codCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_codCli.Leave
        Dim esNIF As Boolean = Verificar_DNI(txt_codCli.Text)
        If esNIF Then
            codCli = Trim(txt_codCli.Text)
            ErrorProvider1.SetError(txt_codCli, Nothing)
        Else
            ErrorProvider1.SetError(txt_codCli, "No es un DNI")
            txt_codCli.Focus()
        End If
    End Sub

    Private Sub txt_nomCli_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_nomCli.KeyPress
        validarNombre(e, txt_nomCli)
    End Sub

    Private Sub txt_nomCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_nomCli.Leave
        nomCli = Trim(txt_nomCli.Text)
    End Sub

    Private Sub txt_apeCli_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txt_apeCli.KeyPress
        ValidarApellidos(e, txt_apeCli)
    End Sub

    Private Sub txt_apeCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_apeCli.Leave
        apeCli = Trim(txt_apeCli.Text)
    End Sub

    Private Sub txt_telCli_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_telCli.KeyPress
        validarNumero(e, txt_telCli)
    End Sub

    Private Sub txt_telCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_telCli.Leave
        telCli = Trim(txt_telCli.Text)
    End Sub

    Private Sub txt_mailCli_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_mailCli.Leave
        If Not (txt_mailCli.Text = Nothing) Then
            If ValidarCorreo(txt_mailCli.Text, txt_mailCli) Then
                mailCli = Trim(txt_mailCli.Text)
                ErrorProvider1.SetError(txt_mailCli, Nothing)
            Else
                ErrorProvider1.SetError(txt_mailCli, "Error de formato")
                txt_mailCli.Focus()
            End If
        End If
    End Sub

    Private Sub txt_nomPac_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_nomPac.KeyPress
        validarNombre(e, txt_nomPac)
    End Sub

    Private Sub txt_nomPac_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_nomPac.Leave
        nomPac = Trim(txt_nomPac.Text)
    End Sub

    Private Sub cbox_tipoPac_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbox_tipoPac.SelectedIndexChanged
        tipoPac = cbox_tipoPac.SelectedItem
    End Sub

    Private Sub txt_raza_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_raza.KeyPress
        ValidarApellidos(e, txt_raza)
    End Sub

    Private Sub txt_raza_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_raza.Leave
        raza = Trim(txt_raza.Text)
    End Sub

    Private Sub SalirToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SalirToolStripMenuItem.Click
        clinica.Show()
        Me.Close()
    End Sub

    Private Sub GuardarToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles GuardarToolStripMenuItem.Click
        controlCita(txt_codCli, txt_codPac.Text)
    End Sub
    '
    ''  FUNCIONES ''
    ''''''''''''''''
    ''' <summary>
    ''' VALIDA QUE EL FORMATO DEL EMAIL SEA CORRECTO,
    ''' QUE CONTENGA UN @ Y UN PUNTO SEGUIDO DE ENTRE
    ''' DOS Y TRES CARACTERES.
    ''' </summary>
    ''' <param name="correo">CADENA A SER EVALUADA</param>
    ''' <param name="txtbox">EL TEXTBOS AL QUE SE DIRIGE EL ERRORPROVIDER</param>
    ''' <returns>DEVUELVE TRUE SI EL FORMATO ES CORRECTO</returns>
    ''' <remarks></remarks>
    Public Function ValidarCorreo(ByVal correo As String, ByVal txtbox As TextBox) As Boolean
        'DESPUES DE IMPORTAR: System.Text.RegularExpressions
        Dim re As New Regex("\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+")
        Dim valido As Boolean
        If re.IsMatch(correo) Then
            valido = True
            ErrorProvider1.SetError(txtbox, Nothing)
        Else
            ErrorProvider1.SetError(txtbox, "Error de formato")
            valido = False
        End If
        re = Nothing
        Return valido
    End Function

    ''' <summary>
    ''' VALIDA QUE EL TEXTO INTRODUCIDO SOLO CONTENGA
    ''' LETRAS Y ESPACIOS EN BLANCO. Y QUITA LOS ESPACIOS
    ''' EN BLANCO QUE SOBRAN
    ''' </summary>
    ''' <param name="e">EVENTO DE TECLA PRECIONADA</param>
    ''' <param name="txtbox">EL TEXTBOX AL QUE SE DIRIGE EL ERRORPROVIDER</param>
    ''' <remarks></remarks>
    Public Sub ValidarApellidos(ByVal e As System.Windows.Forms.KeyPressEventArgs, ByVal txtbox As TextBox)
        'QUITA ESPACIOS EN BLANCO AL PRINCIPIO Y SE ASEGURA QUE SOLO HAYA UNO EN BLANCO ENTRE PALABRAS
        If (Char.IsDigit(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsNumber(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsSymbol(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsPunctuation(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        Else
            If txtbox.Text.StartsWith(" ") Then
                txtbox.Text = txtbox.Text.Trim
                ErrorProvider1.SetError(txtbox, Nothing)
            ElseIf txtbox.Text.EndsWith(" ") Then
                txtbox.Text = txtbox.Text.Trim + " "
                txtbox.SelectionStart = txtbox.Text.Length
            End If
            ErrorProvider1.SetError(txtbox, Nothing)
        End If
    End Sub

    Public Sub validarNombre(ByVal e As System.Windows.Forms.KeyPressEventArgs, ByVal txtbox As TextBox)
        'SOLO ADMITE LETRAS Y ESPACIOS
        If (Char.IsDigit(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsNumber(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsSymbol(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        ElseIf (Char.IsPunctuation(e.KeyChar)) Then
            e.Handled = True
            ErrorProvider1.SetError(txtbox, "Sólo admite letras y espacios")
        Else
            If txtbox.Text.StartsWith(" ") Then
                txtbox.Text = txtbox.Text.Trim
                ErrorProvider1.SetError(txtbox, Nothing)
            ElseIf txtbox.Text.EndsWith(" ") Then
                txtbox.Text = txtbox.Text.Trim + " "
                txtbox.SelectionStart = txtbox.Text.Length
            End If
            ErrorProvider1.SetError(txtbox, Nothing)
        End If
    End Sub

    Private Sub validarNumero(ByVal e As System.Windows.Forms.KeyPressEventArgs, ByVal txtbox As TextBox)
        If e.KeyChar < "0" Or e.KeyChar > "9" Then
            ErrorProvider1.SetError(txtbox, "Sólo admite numerales")
            If e.KeyChar <> Chr(8) Then
                e.KeyChar = Chr(0)
                ErrorProvider1.SetError(txtbox, "Sólo admite numerales")
            Else
                ErrorProvider1.SetError(txtbox, Nothing)
            End If
        Else
            ErrorProvider1.SetError(txtbox, Nothing)
        End If
    End Sub

    Private Function CalculaLetraDNI(ByVal cadena As String) As String
        ' CALCULA LA LETRA DEL DNI Y DELVUELVE EL DNI CORRECTO
        Const myCadena As String = "TRWAGMYFPDXBNJZSQVHLCKE"
        Const myNumeros As String = "0123456789"
        Dim a, b, c, NIF As Integer
        Dim sb As New StringBuilder
        cadena = Trim(cadena)
        If Len(cadena) = 0 Then Return ""
        ' SELECCIONA SOLO LOS NUMEROS
        For i As Integer = 0 To cadena.Length - 1
            If myNumeros.IndexOf(cadena(i)) > -1 Then
                sb.Append(cadena(i))
            End If
        Next
        cadena = sb.ToString
        a = 0
        NIF = CInt(Val(cadena))
        Do
            b = CInt(Int(NIF / 24))
            c = NIF - (24 * b)
            a = a + c
            NIF = b
        Loop While b <> 0
        b = CInt(Int(a / 23))
        c = a - (23 * b)
        Return cadena & Mid(myCadena, CInt(c + 1), 1)
    End Function

    Public Function Verificar_DNI(ByVal cadena As String) As Boolean
        ' COMPRUEBA SI EL DNI ES VALIDO Y DEVUELVE TRUE SI ES CORRECTO
        Dim valido As Boolean
        Dim aux As String
        cadena = cadena.ToUpper 'CAMBIA LA LETRA A MAYUSCULAS
        aux = cadena.Substring(0, cadena.Length - 1) 'SELECCIONA SOLO LOS NUMEROS
        If aux.Length >= 7 AndAlso IsNumeric(aux) Then
            aux = CalculaLetraDNI(aux) 'CALCULA LA LETRA DEL DNI A PARTIR DE LOS NUMEROS
            If cadena <> aux Then 'COMPARA LA LETRA CALCULADA CON LA INGRESADA
                valido = False
            Else
                valido = True
            End If
        End If
        Return valido
    End Function

    Private Function consultaSiExsisten(ByVal pk As String, ByVal tabla As String, ByVal valor As String) As Boolean
        Dim rd As MySqlDataReader
        Dim cmd As New MySqlCommand
        Dim xresult As String = Nothing
        conexion = New MySqlConnection(connectionstring)
        conexion.Open()
        cmd.CommandText = "SELECT " + pk + " FROM " + tabla + " WHERE " + pk + "=@id"
        cmd.Parameters.AddWithValue("@id", valor)
        cmd.Connection = conexion
        rd = cmd.ExecuteReader
        If rd.Read Then
            xresult = rd.GetString(0)
        End If
        conexion.Close()
        If xresult = valor Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Function idMascota(ByVal idMasc As String) As String
        Dim rd As MySqlDataReader
        Dim cmd As New MySqlCommand
        Dim xresult As String = Nothing
        conexion = New MySqlConnection(connectionstring)
        conexion.Open()
        cmd.CommandText = "SELECT id FROM mascota where id=@id"
        cmd.Parameters.AddWithValue("@id", idMasc)
        cmd.Connection = conexion
        rd = cmd.ExecuteReader
        If rd.Read Then
            xresult = rd.GetString(0)
        End If
        conexion.Close()
        Return xresult
    End Function

    Private Sub guardarCita(ByVal pkMascota As String)
        Dim si As String = "Si"
        Try
            conexion = New MySqlConnection(connectionstring)
            Dim insertCita As MySqlCommand
            Dim oredenCita As String = "INSERT INTO citaprevia(horacita,fechacita,atendido,mascota)VALUES(@horacita,@fechacita,@atendido,@mascota)"
            insertCita = New MySqlCommand(oredenCita, conexion)
            With insertCita
                Try
                    .Parameters.AddWithValue("@horacita", dtp_hora.Text)
                    .Parameters.AddWithValue("@fechacita", dtp_fecha.Text)
                Catch ex As Exception
                    MsgBox("Error al guaradar FECHA de la cita", vbCritical, "ERROR")
                    Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
                    Dim sw As New StreamWriter(fs)
                    sw.WriteLine("Error al guaradar FECHA cita" + ex.Message.ToString)
                    sw.Close()
                    fs.Close()
                End Try
                .Parameters.AddWithValue("@atendido", si)
                .Parameters.AddWithValue("@mascota", pkMascota)
            End With

            conexion.Open()
            insertCita.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox("Error al guardar Cita " + ex.Message.ToString, vbCritical, "ERROR")
            Dim fs As New FileStream(".\logzoo.txt", FileMode.Append, FileAccess.Write)
            Dim sw As New StreamWriter(fs)
            sw.WriteLine("Error al guardar Cita " + ex.Message.ToString)
            sw.Close()
            fs.Close()
            conexion.Close()
        Finally
            conexion.Close()
        End Try
    End Sub

    Private Sub controlCita(ByVal dni As TextBox, ByVal codpac As String)
        If dni.TextLength > 0 Then
            Dim existeCliente As Boolean = consultaSiExsisten("dni", "cliente", dni.Text)
            Select Case existeCliente
                Case True
                    Dim exsisteMascota As Boolean = consultaSiExsisten("id", "mascota", codpac)
                    Select Case exsisteMascota
                        Case True
                            If idMascota(txt_codPac.Text) <> "NO" Then
                                guardarCita(idMascota(txt_codPac.Text))
                            Else
                                MsgBox("Error en integridad de la base de datos. Consulte con el servicio técnico.", vbCritical, "Error en la Base de Datos")
                            End If
                        Case False
                            MsgBox("La mascota no está registrada o su código no es el correcto.")
                    End Select
                Case False
                    MsgBox("El cliente no está registrado")
            End Select
        Else
            MsgBox("El campo de DNI de Cliente es obligatorio", vbCritical, "ERROR")
        End If
        table.Clear()
        adapter.Fill(table)
        dgv_citPre.DataSource = table
        dgv_citPre.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
    End Sub

    Private Sub NuevoToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles NuevoToolStripMenuItem.Click
        txt_apeCli.Text = ""
        txt_codCli.Text = ""
        txt_codPac.Text = ""
        txt_mailCli.Text = ""
        txt_nomCli.Text = ""
        txt_nomPac.Text = ""
        txt_raza.Text = ""
        txt_telCli.Text = ""
    End Sub
End Class